package com.s4j.struts.biz.service;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.s4j.core.hibernate.CommonDao;

@Service
public class LiverAnalyService {
	
	@Resource
	private CommonDao dao;

	/**
	 * 肝硬化发病年龄统计
	 */
	public List<Object[]> liverAnaly_ageAnaly() {
		int[] age = new int[]{15,25,35,45,55,65};
		
		String sql = "select * from ( " +
				"	select 1 as ordernum, " +
				"		'15岁及以下', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 15 " +
				" " +
				"	union all  " +
				" " +
				"	select 2 as ordernum, " +
				"		'16-25', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 25 " +
				"	and la.quezhen_age > 15 " +
				" " +
				"	union all  " +
				" " +
				"	select 3 as ordernum, " +
				"		'26-35', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 35 " +
				"	and la.quezhen_age > 25 " +
				" " +
				"	union all  " +
				" " +
				"	select 4 as ordernum, " +
				"		'36-45', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 45 " +
				"	and la.quezhen_age > 35 " +
				" " +
				"	union all  " +
				" " +
				"	select 5 as ordernum, " +
				"		'46-55', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 55 " +
				"	and la.quezhen_age > 45 " +
				" " +
				"	union all  " +
				" " +
				"	select 6 as ordernum, " +
				"		'56-65', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age <= 65 " +
				"	and la.quezhen_age > 55 " +
				" " +
				"	union all  " +
				" " +
				"	select 7 as ordernum, " +
				"		'66岁及以上', " +
				"		count(1) " +
				"	from biz_patientdoc t, " +
				"		biz_li_appr la " +
				"	where t.diseaseType = 1 " +
				"	and t.docid = la.patientId " +
				"	and la.quezhen_age >65 " +
				") t1 " +
				"order by t1.ordernum ";
		
		List<Object[]> resList = (List<Object[]>)dao.queryBySql(sql, null);
		return resList;
	}
	
	public List<Object[]> liverAnaly_averageAnaly() {
		
		String sql = "select left(m.quezhen_day, 4), " +
				" ifnull(AVG(m.quezhen_age),0) as avg_age " +
				" from biz_patientdoc p, " +
				"	biz_li_appr m " +
				" where p.docId = m.patientId " +
				" and p.diseaseType = 1 " +
				" and m.quezhen_day is not NULL " +
				" group by left(m.quezhen_day, 4) " +
				" order by left(m.quezhen_day, 4) ";
		
		List<Object[]> resList = (List<Object[]>)dao.queryBySql(sql, null);
		return resList;
	}
	
	public List<Object[]> liverAnaly_sexualAnaly() {
		
		String sql = "select if(p.sex = 1,'男','女'), count(*) " +
				" from biz_patientdoc p " +
				" where p.diseaseType = 1 " +
				" and p.sex is not null " +
				" group by p.sex ";
		
		List<Object[]> resList = (List<Object[]>)dao.queryBySql(sql, null);
		return resList;
	}
}
